package com.hr.user.dao.Impl;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import com.hr.user.dao.UserDao;
import com.hr.user.domain.User;
import com.hr.utils.JdbcUtils;
import com.hr.utils.PageBean;
import com.hr.utils.TxQueryRunner;

public class UserDaoImpl implements UserDao{
	private QueryRunner qr = new TxQueryRunner();

	@Override
	public User findByUsername(String username) {
		// TODO Auto-generated method stub
		try {
			String sql = "select * from HR_USER where username=?";
			return qr.query(sql, new BeanHandler<User>(User.class), username);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}

	@Override
	public User findByUUID(String uuid) {
		// TODO Auto-generated method stub
		try {
			String sql = "select * from HR_USER where USER_ID=?";
			return qr.query(sql, new BeanHandler<User>(User.class), uuid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}

	@Override
	public List<User> queryAll() {
		// TODO Auto-generated method stub
		try {
			String sql = "select * from HR_USER";
			return qr.query(sql, new BeanListHandler<User>(User.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}

	@Override
	public void addUser(User user) {
		// TODO Auto-generated method stub
		Date now = new Date(); 
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//可以方便地修改日期格式
		String nowTime = dateFormat.format(now);
		try {
			String sql = "insert into HR_USER values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			Object[] params = {user.getUser_id(),user.getUsername(),user.getPassword(),user.getSex(),user.getAge(),
					user.getState(),user.getPhoto(),user.getUserfile(),user.getDept_id(),user.getFilename(),user.getUser_id(),
					user.getVerified_person(),user.getEducation_record(),user.getDegree()};
			String sql_dept_user = "insert into DEPT_USER values('"+user.getDept_id()+"','"+user.getUser_id()+"','" +
					"正式入职"+"','1',to_date('"+nowTime+"','YYYY-MM-DD HH24:MI:SS'))";
			qr.update(sql,params);
			qr.update(sql_dept_user);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	public void updatePassword(User user,String password) {
		// TODO Auto-generated method stub
		String sql = "update HR_USER set password=? where user_id=?";
		try {
			qr.update(sql,password,user.getUser_id());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	public PageBean<User> queryAll_Page(int PageCurrent, int PageSize) {
		// TODO Auto-generated method stub
		try {
			PageBean<User> pb = new PageBean<User>();
			//设置当前页面页码
			pb.setPageCurrent(PageCurrent);
			//设置当前页面的页面容量
			pb.setPageSize(PageSize);
			String sql = "select Count(*) from HR_USER";
			Number num = (Number)qr.query(sql, new ScalarHandler());
			int rowCount = num.intValue();//获取数据库中的所有记录数
			pb.setRowCount(rowCount);
			sql = "select * from (select a1.*,rownum rn from (select * from HR_USER)a1 where rownum<="+PageCurrent*PageSize+")where rn >="+((PageCurrent-1)*PageSize+1);
			List<User> list_user = qr.query(sql, new BeanListHandler<User>(User.class));
			pb.setBeanList(list_user);
			return pb;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}

	@Override
	public void deleteByUserID(String user_id) {
		// TODO Auto-generated method stub
		try {
			String sql = "delete HR_USER where user_id=?";
			qr.update(sql, user_id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	public void updateUser(User user) {
		// TODO Auto-generated method stub
		Date now = new Date(); 
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//可以方便地修改日期格式
		String nowTime = dateFormat.format(now);
		try {
			String sql = "update HR_USER set " +
					"username=?,sex=?,age=?,state=?,photo=?,userfile=?,dept_id=?,filename=? where user_id=?";
			Object[] params = {user.getUsername(),user.getSex(),user.getAge(),user.getState(),user.getPhoto(),
					user.getUserfile(),user.getDept_id(),user.getFilename(),user.getUser_id()};
			qr.update(sql, params);
			String sql_dept="insert into DEPT_USER values('"+user.getDept_id()+"','"+user.getUser_id()+"','" +
					"管理员信息修改调任"+"','1',to_date('"+nowTime+"','YYYY-MM-DD HH24:MI:SS'))";
			qr.update(sql_dept);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Override
	public PageBean<User> queryAll_Page_Condition(int pageCurrent,
			int pageSize, String username, String dept_id) {
		// TODO Auto-generated method stub
		try {
			PageBean<User> pb = new PageBean<User>();
			//设置当前页面页码
			pb.setPageCurrent(pageCurrent);
			//设置当前页面的页面容量
			pb.setPageSize(pageSize);
			StringBuilder sql = new StringBuilder();
			sql.append("select Count(*) from HR_USER");
			sql.append(" where 1=1 ");
			if(username != null){
				sql.append("and username Like '%"+username+"%' ");
			}
			if(!dept_id.equals("null")){
				sql.append("and dept_id = '"+dept_id+"' ");
			}
			Number num = (Number)qr.query(sql.toString(), new ScalarHandler());
			int rowCount = num.intValue();//获取数据库中的所有记录数
			pb.setRowCount(rowCount);
			StringBuilder sql_query = new StringBuilder();
			sql_query.append("select * from (select a1.*,rownum rn from (select * from HR_USER ");
			sql_query.append(" where 1=1 ");
			if(username != null){
				sql_query.append("and username Like '%"+username+"%' ");
			}
			if(!dept_id.equals("null")){
				sql_query.append("and dept_id = '"+dept_id+"' ");
			}
			sql_query.append(")a1 where rownum<=");
			String sql_end = null;
			sql_end = sql_query.toString()+pageCurrent*pageSize+")where rn >="+((pageCurrent-1)*pageSize+1);
			List<User> list_user = qr.query(sql_end, new BeanListHandler<User>(User.class));
			pb.setBeanList(list_user);
			return pb;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}

	@Override
	public PageBean<User> queryAll_PageAndDept(int PageCurrent, int PageSize, String dept_id) {
		try {
			PageBean<User> pb = new PageBean<User>();
			//设置当前页面页码
			pb.setPageCurrent(PageCurrent);
			//设置当前页面的页面容量
			pb.setPageSize(PageSize);
			String sql = "select Count(*) from HR_USER where dept_id=?";
			Number num = (Number)qr.query(sql, new ScalarHandler(),dept_id);
			int rowCount = num.intValue();//获取数据库中的所有记录数
			pb.setRowCount(rowCount);
			sql = "select * from (select a1.*,rownum rn from (select * from HR_USER where dept_id=?)a1 where rownum<="+PageCurrent*PageSize+")where rn >="+((PageCurrent-1)*PageSize+1);
			List<User> list_user = qr.query(sql, new BeanListHandler<User>(User.class),dept_id);
			pb.setBeanList(list_user);
			return pb;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

}
